package org.istt.web;


import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletContext;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.struts2.ServletActionContext;
import org.istt.domain.Department;
import org.istt.domain.SubCategory;
import org.istt.export.ReceivedFiles;
import org.istt.model.FileBean;
import org.istt.model.FileReportBean;
import org.istt.model.GeneralReportBean;
import org.istt.service.DepartmentManager;
import org.istt.service.FileManager;
import org.istt.service.SubCategoryManager;
import org.jfree.util.Log;
import org.springframework.beans.factory.annotation.Autowired;

import com.opensymphony.xwork2.ActionSupport;
/**
 *
 * @author 
 */
public class ExportStatisticReport extends ActionSupport{
	
	private static final long serialVersionUID = 3338690462530238561L;
	InputStream excelStream;
//	String documentFormat = "xlsx"; //For Office Excel 2007
	String documentExcelFormat = "xls"; //For Office Excel 2003
	private String reportType;
	@Autowired
	private FileManager fileManager;

	@Autowired
	private DepartmentManager departmentManager;
	
	@Autowired
	private SubCategoryManager subCategoryManager;
	
	
	private List<Department> lstDepartment;
	
	public FileManager getFileManager() {
		return fileManager;
	}

	public void setFileManager(FileManager fileManager) {
		this.fileManager = fileManager;
	}

	public String excel() throws IOException, InvalidFormatException {
	    
	    if(reportType!=null){
	    	if(WebConstants.REPORT_TYPE_PROCESS_OVER_TIME.equals(reportType) || WebConstants.REPORT_TYPE_PROCESS_NOT_APPROVED.equals(reportType)) return generateReportByDepartment();
	    	else if(WebConstants.STATISTICS_BY_PERIOD.equals(reportType)) return generateReportByPeriod();
	    }

	    return SUCCESS;
	}
	
	
	InputStream wordStream;
	String documentDocFormat = "doc";
	String getWordContentType() {
		return documentExcelFormat == "doc" ? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" : "application/msword";
	}
	
	public InputStream getWordStream() {
		return wordStream;
	}
	
	private String fromDate;
	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}

	private String deptId;

	private String toDate;
	
	/***
	 * Generate list of received files by Date
	 * @return
	 * @throws ParseException 
	 * @throws IOException 
	 */
	public String getListReceivedFilesByDate() throws ParseException, IOException{
		
		if(fromDate!=null && !"".equals(fromDate) && toDate!=null && !"".equals(toDate)){
			Date f = StringUtils.isEmpty(fromDate) ? null : DateUtils.parseDate(
					fromDate, new String[] { "dd/MM/yyyy hh:mm" });
			Date t = StringUtils.isEmpty(toDate) ? null : DateUtils.parseDate(
					toDate, new String[] {"dd/MM/yyyy hh:mm"});
			
			if(deptId!=null && !"".equals(deptId)){
				Department dept = this.departmentManager.get(Long.parseLong(deptId));
				List<org.istt.domain.File> files = this.fileManager.getReceivedFileByDate(Long.parseLong(deptId), f, t);
				for (Iterator iterator = files.iterator(); iterator.hasNext();) {
					org.istt.domain.File file = (org.istt.domain.File) iterator.next();
					
					if(file.getSubCategory() != null)
						file.setSubCategory(subCategoryManager.get(file.getSubCategory().getId()));
				}
				wordStream = new ReceivedFiles().toMSWord(getFile("/word/Danh_sach_ho_so_moi_TN.docx"), files,dept.getName(), null, f, t);

				return SUCCESS;
			}
		}
		
		return ERROR;
	}
	
	InputStream getFile(String path) throws IOException {
		ServletContext servletContext = ServletActionContext.getServletContext();
	    return new FileInputStream(new File(servletContext.getRealPath(path)));
	}
	
	private String generateReportByPeriod() throws IOException{
		ServletContext servletContext = ServletActionContext.getServletContext();
	    String filePath = servletContext.getRealPath("/excel/BAO_CAO_TONG_HOP.xls");
	   
	    File file = new File(filePath);
 	    InputStream inp = new FileInputStream(file);
// 	    XSSFWorkbook wb = new XSSFWorkbook(inp); //For Office Excel 2007
 	   HSSFWorkbook wb = new HSSFWorkbook(inp);//For Office Excel 2003
 	     
 	    
		@SuppressWarnings("unchecked")
		List<GeneralReportBean> lstFiles = (List<GeneralReportBean>)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_REPORT);
	    if(lstFiles!=null && lstFiles.size()!=0){
	    	int START_ROW  = 13;
	    	HSSFSheet sheet = wb.getSheetAt(0);

	 	    String fromdate = (String)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_FROMDATE);
			String todate = (String)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_TODATE);
			String totalInMonth = (String)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_TOTAL_RECEIVED_INMONTH);
			String totalLastMonth = (String)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_TOTAL_RECEIVED_FROM_LASTMONTH);
			String strDate = "( " + getText("statistic.excel.fromdate") + " "
							+ fromdate + " " + getText("statistic.excel.todate")+ " " + todate + " )";
			DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
			Date today = new Date();
			String strToday = getText("statistic.excel.location") + getText("statistic.excel.day") + " "  + dateFormat.format(today);
			
			Font header_font = wb.createFont();
			header_font.setFontHeightInPoints((short)FONT_SIZE);
			header_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
			header_font.setBoldweight(Font.BOLDWEIGHT_BOLD);

			Font body_font = wb.createFont();
			body_font.setFontHeightInPoints((short)FONT_SIZE);
			body_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
			body_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			
			// Fonts are set into a style so create a new one to use.
			HSSFCellStyle BODY_STYLE = wb.createCellStyle();
			BODY_STYLE.setFont(body_font);
			BODY_STYLE.setBorderLeft(CellStyle.BORDER_THIN);
			BODY_STYLE.setBorderBottom(CellStyle.BORDER_THIN);
			BODY_STYLE.setBorderRight(CellStyle.BORDER_THIN);
			BODY_STYLE.setBorderTop(CellStyle.BORDER_THIN);
			BODY_STYLE.setFillBackgroundColor(new HSSFColor.YELLOW().getIndex());

			Font content_font = wb.createFont();
			content_font.setFontHeightInPoints((short)FONT_SIZE);
			content_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
			HSSFCellStyle CONTENT_STYLE = wb.createCellStyle();
			CONTENT_STYLE.setFont(content_font);
			CONTENT_STYLE.setBorderLeft(CellStyle.BORDER_THIN);
			CONTENT_STYLE.setBorderBottom(CellStyle.BORDER_THIN);
			CONTENT_STYLE.setBorderRight(CellStyle.BORDER_THIN);
			CONTENT_STYLE.setBorderTop(CellStyle.BORDER_THIN);

			Font footer_font = wb.createFont();
			footer_font.setFontHeightInPoints((short)14);
			footer_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
			footer_font.setItalic(true);
			HSSFCellStyle FOOTER_STYLE = wb.createCellStyle();
			FOOTER_STYLE.setFont(footer_font);
			

			HSSFCellStyle HEADER_STYLE = wb.createCellStyle();
			HEADER_STYLE.setFont(header_font);
			HEADER_STYLE.setAlignment(CellStyle.ALIGN_CENTER);
			
			/* HEADER */
			HSSFRow row_header_1 = sheet.createRow(3);
			HSSFCell header_cell_1 = row_header_1.createCell(4);
			header_cell_1.setCellValue(strToday.toUpperCase());
			header_cell_1.setCellStyle(HEADER_STYLE);
			
			HSSFRow row_header_2 = sheet.createRow(5);
			HSSFCell header_cell_2 = row_header_2.createCell(0);
			header_cell_2.setCellValue(strDate.toUpperCase());
			header_cell_2.setCellStyle(HEADER_STYLE);
			
			HSSFRow row_header_3 = sheet.createRow(7);
			HSSFCell header_cell_label_lastmonth = row_header_3.createCell(9);
			header_cell_label_lastmonth.setCellValue(getText("statistic.excel.lastmonth"));
			header_cell_label_lastmonth.setCellStyle(HEADER_STYLE);

			HSSFCell header_cell_value_lastmonth = row_header_3.createCell(11);
			header_cell_value_lastmonth.setCellStyle(HEADER_STYLE);
			header_cell_value_lastmonth.setCellValue(totalLastMonth);	
			
			HSSFCell header_cell_label_file_1 = row_header_3.createCell(12);
			header_cell_label_file_1.setCellValue(getText("statistic.excel.file"));
			header_cell_label_file_1.setCellStyle(HEADER_STYLE);

			HSSFRow row_header_4 = sheet.createRow(8);
			HSSFCell header_cell_label_inmonth = row_header_4.createCell(9);
			header_cell_label_inmonth.setCellValue(getText("statistic.excel.inmonth"));
			header_cell_label_inmonth.setCellStyle(HEADER_STYLE);

			HSSFCell header_cell_value_inmonth = row_header_4.createCell(11);
			header_cell_value_inmonth.setCellValue(totalInMonth);
			header_cell_value_inmonth.setCellStyle(HEADER_STYLE);

			HSSFCell header_cell_label_file_2 = row_header_4.createCell(12);
			header_cell_label_file_2.setCellValue(getText("statistic.excel.file"));
			header_cell_label_file_2.setCellStyle(HEADER_STYLE);
			
			/* TITLE */
			GeneralReportBean fileHeader = lstFiles.get(0);
			generateRow(sheet, BODY_STYLE, fileHeader, START_ROW, 0,body_font);
			
			int depCount = 0;
			int categoryCount = 0;
			/* BODY */
	 		for (int rowcount = 1; rowcount < lstFiles.size(); rowcount++) {
	 			
	 			GeneralReportBean fileObj = lstFiles.get(rowcount);
	 			//generateRow(sheet, BODY_STYLE, fileDetail, START_ROW+rowcount, rowcount,body_font);
	 			HSSFRow row = sheet.createRow(START_ROW+rowcount);
	 			HSSFCellStyle STYLE = wb.createCellStyle();
	 			/*STT*/
				String cellVal = String.valueOf(rowcount);
				if (fileObj.getField().equals(WebConstants.SUM)) {
					cellVal = String.valueOf(rowcount);
					STYLE = BODY_STYLE;
				} else if (fileObj.getField().equals(WebConstants.DEP_NAME)) {
					char cno = (char) (65 + depCount);
					cellVal = Character.toString(cno);
					depCount++;
					categoryCount = 0;
					STYLE = BODY_STYLE;
				}else if (fileObj.getField().equals(WebConstants.CATE_NAME)) {
					categoryCount++;
					cellVal = String.valueOf(categoryCount);
					STYLE = CONTENT_STYLE;
				}
				HSSFCell cell_stt = row.createCell(0);
				cell_stt.setCellValue(cellVal);
				cell_stt.setCellStyle(STYLE);

				/*FULL NAME*/
	 			HSSFCell cell_fullname = row.createCell(1);
	 			cell_fullname.setCellValue(fileObj.getCategory());
	 			cell_fullname.setCellStyle(STYLE);

	 			HSSFCell cell_TotalInMonth = row.createCell(2);
	 			cell_TotalInMonth.setCellValue(fileObj.getTotalInMonth().equals("0")?"":fileObj.getTotalInMonth());
	 			cell_TotalInMonth.setCellStyle(STYLE);

	 			
	 			HSSFCell cell_ResolvedInMonth = row.createCell(3);
	 			cell_ResolvedInMonth.setCellStyle(STYLE);
	 			if(fileObj.getResolvedInMonth()!=0){
	 				cell_ResolvedInMonth.setCellValue(fileObj.getResolvedInMonth());
	 			}

	 			HSSFCell cell_ResolvedOnTime = row.createCell(4);
	 			cell_ResolvedOnTime.setCellStyle(STYLE);
	 			if(fileObj.getResolvedOnTime()!=0){
	 				cell_ResolvedOnTime.setCellValue(fileObj.getResolvedOnTime());
	 			}

	 			HSSFCell cell_TotalOverTime = row.createCell(5);
	 			cell_TotalOverTime.setCellStyle(STYLE);
	 			if (fileObj.getTotalOverTime()!=0) {
	 				cell_TotalOverTime.setCellValue(fileObj.getTotalOverTime());
	 			}

	 			HSSFCell cell_Receiver = row.createCell(6);
	 			cell_Receiver.setCellStyle(STYLE);
	 			if (fileObj.getOverTimeLessThanFive()!=0) {
	 				cell_Receiver.setCellValue(fileObj.getOverTimeLessThanFive());
	 			}

	 			HSSFCell cell_outputDate = row.createCell(7);
	 			cell_outputDate.setCellStyle(STYLE);
	 			if (fileObj.getOverTimeBetweenFiveAndTen()!=0) {
	 				cell_outputDate.setCellValue(fileObj.getOverTimeBetweenFiveAndTen());
	 			}

	 			HSSFCell cell_OverTimeBetweenTenAndTwenty = row.createCell(8);
	 			cell_OverTimeBetweenTenAndTwenty.setCellStyle(STYLE);
	 			if (fileObj.getOverTimeBetweenTenAndTwenty()!=0) {
	 				cell_OverTimeBetweenTenAndTwenty.setCellValue(fileObj
	 						.getOverTimeBetweenTenAndTwenty());
	 			}

	 			HSSFCell cell_OverTimeMoreThanTwenty = row.createCell(9);
	 			cell_OverTimeMoreThanTwenty.setCellStyle(STYLE);
	 			if (fileObj.getOverTimeMoreThanTwenty()!=0) {
	 				cell_OverTimeMoreThanTwenty.setCellValue(fileObj
	 						.getOverTimeMoreThanTwenty());
	 			}

	 			HSSFCell cell_TotalNotApproved = row.createCell(10);
	 			cell_TotalNotApproved.setCellStyle(STYLE);
	 			if (fileObj.getTotalNotApproved()!=0) {
	 				cell_TotalNotApproved.setCellValue(fileObj.getTotalNotApproved());
	 			}

	 			HSSFCell cell_NotApprovedBeforeDeadline = row.createCell(11);
	 			cell_NotApprovedBeforeDeadline.setCellStyle(STYLE);
	 			if (fileObj.getNotApprovedBeforeDeadline()!=0) {
	 				cell_NotApprovedBeforeDeadline.setCellValue(fileObj
	 						.getNotApprovedBeforeDeadline());
	 			}
	 			
	 			HSSFCell cell_NotApprovedOverdue = row.createCell(12);
	 			cell_NotApprovedOverdue.setCellStyle(STYLE);
	 			if (fileObj.getNotApprovedOverdue()!=0) {
	 				cell_NotApprovedOverdue.setCellValue(fileObj.getNotApprovedOverdue());
	 			}

	 		}	
	 		
	 		HSSFRow row_footer = sheet.createRow(START_ROW+lstFiles.size());
	 		HSSFCell cell_sendto = row_footer.createCell(1);
	 		cell_sendto.setCellValue(getText("statistic.excel.location.sendto"));
	 		cell_sendto.setCellStyle(FOOTER_STYLE);

	 		HSSFCell cell_author = row_footer.createCell(2);
	 		cell_author.setCellValue(getText("statistic.excel.author"));
	 		cell_author.setCellStyle(FOOTER_STYLE);

	 		HSSFCell cell_manager = row_footer.createCell(9);
	 		cell_manager.setCellValue(getText("statistic.excel.manager"));
	 		cell_manager.setCellStyle(FOOTER_STYLE);
	 		
	    }else{
	    	 System.out.println("LIST EMPTY!");
	    }
	        
	    //write to excel file
	    ByteArrayOutputStream baos = new ByteArrayOutputStream();
	    wb.write(baos);
	    excelStream = new ByteArrayInputStream(baos.toByteArray());
		return SUCCESS;
	}
	
	private void generateRow( HSSFSheet sheet,HSSFCellStyle BODY_STYLE,GeneralReportBean fileObj,int rownumber,int no,Font body_font){
		HSSFRow row = sheet.createRow(rownumber);
		if(fileObj.getField()!=null && fileObj.getField().equals(WebConstants.SUM)){
			body_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	  	}else{
	  		body_font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
	  	}
		
		if(no > 0){
			char cno = (char)(64+no);
			String value = String.valueOf(no);
			if(fileObj.getField()!=null && fileObj.getField().equals(WebConstants.DEP_NAME)){
				value = Character.toString(cno);
			}
			HSSFCell cell_stt = row.createCell(0);
	  	    cell_stt.setCellValue(value);
	  	    cell_stt.setCellStyle(BODY_STYLE);
		}

		HSSFCell cell_fullname = row.createCell(1);
		cell_fullname.setCellValue(fileObj.getCategory());
		cell_fullname.setCellStyle(BODY_STYLE);

		HSSFCell cell_TotalInMonth = row.createCell(2);
		cell_TotalInMonth.setCellValue(fileObj.getTotalInMonth().equals("0")?"":fileObj.getTotalInMonth());
		cell_TotalInMonth.setCellStyle(BODY_STYLE);

		
		if(fileObj.getResolvedInMonth()!=0){
			HSSFCell cell_ResolvedInMonth = row.createCell(3);
			cell_ResolvedInMonth.setCellValue(fileObj.getResolvedInMonth());
			cell_ResolvedInMonth.setCellStyle(BODY_STYLE);
		}

		if(fileObj.getResolvedOnTime()!=0){
			HSSFCell cell_ResolvedOnTime = row.createCell(4);
			cell_ResolvedOnTime.setCellValue(fileObj.getResolvedOnTime());
			cell_ResolvedOnTime.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getTotalOverTime()!=0) {
			HSSFCell cell_TotalOverTime = row.createCell(5);
			cell_TotalOverTime.setCellValue(fileObj.getTotalOverTime());
			cell_TotalOverTime.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getOverTimeLessThanFive()!=0) {
			HSSFCell cell_Receiver = row.createCell(6);
			cell_Receiver.setCellValue(fileObj.getOverTimeLessThanFive());
			cell_Receiver.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getOverTimeBetweenFiveAndTen()!=0) {
			HSSFCell cell_outputDate = row.createCell(7);
			cell_outputDate.setCellValue(fileObj.getOverTimeBetweenFiveAndTen());
			cell_outputDate.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getOverTimeBetweenTenAndTwenty()!=0) {
			HSSFCell cell_OverTimeBetweenTenAndTwenty = row.createCell(8);
			cell_OverTimeBetweenTenAndTwenty.setCellValue(fileObj
					.getOverTimeBetweenTenAndTwenty());
			cell_OverTimeBetweenTenAndTwenty.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getOverTimeMoreThanTwenty()!=0) {
			HSSFCell cell_OverTimeMoreThanTwenty = row.createCell(9);
			cell_OverTimeMoreThanTwenty.setCellValue(fileObj
					.getOverTimeMoreThanTwenty());
			cell_OverTimeMoreThanTwenty.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getTotalNotApproved()!=0) {
			HSSFCell cell_TotalNotApproved = row.createCell(10);
			cell_TotalNotApproved.setCellValue(fileObj.getTotalNotApproved());
			cell_TotalNotApproved.setCellStyle(BODY_STYLE);
		}

		if (fileObj.getNotApprovedBeforeDeadline()!=0) {
			HSSFCell cell_NotApprovedBeforeDeadline = row.createCell(11);
			cell_NotApprovedBeforeDeadline.setCellValue(fileObj
					.getNotApprovedBeforeDeadline());
			cell_NotApprovedBeforeDeadline.setCellStyle(BODY_STYLE);
		}
		
		if (fileObj.getNotApprovedOverdue()!=0) {
			HSSFCell cell_NotApprovedOverdue = row.createCell(12);
			cell_NotApprovedOverdue.setCellValue(fileObj.getNotApprovedOverdue());
			cell_NotApprovedOverdue.setCellStyle(BODY_STYLE);
		}

	}
	
	private String generateReportByDepartment() throws IOException{
		ServletContext servletContext = ServletActionContext.getServletContext();
		String filePath = servletContext.getRealPath("/excel/DS_HOSO_CHUA_GIAI_QUYET.xls");
		
		File file = new File(filePath);
		InputStream inp = new FileInputStream(file);
		HSSFWorkbook wb = new HSSFWorkbook(inp);
		
		@SuppressWarnings("unchecked")
		//Get list of files from Servlet Context
		List<FileReportBean> lstFiles = (List<FileReportBean>)servletContext.getAttribute(WebConstants.SERVLET_CONTEXT_REPORT);
		String reportType = (String)servletContext.getAttribute(WebConstants.REPORT_TYPE);
		
		if(lstFiles!=null && lstFiles.size()!=0){
			Log.debug("Export report to excel...");
			HSSFSheet sheet = wb.getSheetAt(0);
			//TODO: Create font
			/* BODY */
			int cursorLocation = 1;
			for (Iterator<FileReportBean> iterator = lstFiles.iterator(); iterator.hasNext();) {
				FileReportBean fileReportBean = (FileReportBean) iterator.next();
				
				//TODO: Generate report for each department
				generateDeptReport(wb, sheet,fileReportBean,cursorLocation,reportType);
				
				// Update next location of cursor
				cursorLocation = fileReportBean.getLstFile().size() + GAP_BETWEEN_TABLES;
			}
			
			//write report data to file
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			wb.write(baos);
			excelStream = new ByteArrayInputStream(baos.toByteArray());
			
		}else{
			addActionError(getText("category.errormsg.code.is.existed"));
		}
		
		return SUCCESS;
	}
	
	private final static int GAP_BETWEEN_TABLES = 5;
	
	private void generateTableHeader(HSSFSheet sheet,HSSFCellStyle style,int startRow,String reportType,String deptName){
		if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_NOT_APPROVED)){
			sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, COL_1, COL_9));
		}else if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_OVER_TIME)){
			sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, COL_1, COL_11));
		}

		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		
		/* HEADER */
		HSSFRow row_header = sheet.createRow(startRow);
		HSSFCell header_cell_1 = row_header.createCell(0);
		header_cell_1.setCellValue(getText("reportByDepartment.reporttype.1.excel.header.title")+deptName.toUpperCase());
		header_cell_1.setCellStyle(style);

		// Column: STT
		HSSFRow row_header_1 = sheet.createRow(startRow+1);
		HSSFCell header_stt = row_header_1.createCell(COL_1);
		header_stt.setCellValue(getText("reportByDepartment.excel.table.header.STT"));
		header_stt.setCellStyle(style);

		// Column: Sender
		HSSFCell headerCell_Sender = row_header_1.createCell(COL_2);
		headerCell_Sender.setCellValue(getText("reportByDepartment.excel.table.header.SENDER"));
		headerCell_Sender.setCellStyle(style);

		// Column: Location
		HSSFCell headerCell_Location = row_header_1.createCell(COL_3);
		headerCell_Location.setCellValue(getText("reportByDepartment.excel.table.header.LOCATION"));
		headerCell_Location.setCellStyle(style);

		// Column: Category
		HSSFCell headerCell_Category = row_header_1.createCell(COL_4);
		headerCell_Category.setCellValue(getText("reportByDepartment.excel.table.header.CATEGORY"));
		headerCell_Category.setCellStyle(style);

		// Column: Input date
		HSSFCell headerCell_InputDate = row_header_1.createCell(COL_5);
		headerCell_InputDate.setCellValue(getText("reportByDepartment.excel.table.header.INPUTDATE"));
		headerCell_InputDate.setCellStyle(style);
		
		// Column: Process Input date
		HSSFCell headerCell_Process_InputDate = row_header_1.createCell(COL_6);
		headerCell_Process_InputDate.setCellValue(getText("reportByDepartment.excel.table.header.DEV.PROCESS.INPUTDATE"));
		headerCell_Process_InputDate.setCellStyle(style);
		
		// Column: Process Receiver
		HSSFCell headerCell_Process_Receiver = row_header_1.createCell(COL_7);
		headerCell_Process_Receiver.setCellValue(getText("reportByDepartment.excel.table.header.DEV.PROCESS.RECEIVER"));
		headerCell_Process_Receiver.setCellStyle(style);

		if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_NOT_APPROVED)){
			//TODO: exclude 2 columns: received date and receiver of onegate
			// Column: get result date
			HSSFCell headerCell_OutputDate = row_header_1.createCell(COL_8);
			headerCell_OutputDate.setCellValue(getText("reportByDepartment.excel.table.header.OUTPUTDATE"));
			headerCell_OutputDate.setCellStyle(style);

			// Column: result
			HSSFCell headerCell_Result = row_header_1.createCell(COL_9);
			headerCell_Result.setCellValue(getText("reportByDepartment.excel.table.header.RESULT"));
			headerCell_Result.setCellStyle(style);
		}else if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_OVER_TIME)){
			//TODO: include 2 columns: received date and receiver of onegate
			// Column: Onegate Receiver
			HSSFCell headerCell_Onegate_Inputdate = row_header_1.createCell(COL_8);
			headerCell_Onegate_Inputdate.setCellValue(getText("reportByDepartment.excel.table.header.DEV.ONEGATE.INPUTDATE"));
			headerCell_Onegate_Inputdate.setCellStyle(style);

			// Column: received date
			HSSFCell headerCell_Onegate_Receiver = row_header_1.createCell(COL_9);
			headerCell_Onegate_Receiver.setCellValue(getText("reportByDepartment.excel.table.header.DEV.ONEGATE.RECEIVER"));
			headerCell_Onegate_Receiver.setCellStyle(style);
			
			// Column: get result date
			HSSFCell headerCell_OutputDate = row_header_1.createCell(COL_10);
			headerCell_OutputDate.setCellValue(getText("reportByDepartment.excel.table.header.OUTPUTDATE"));
			headerCell_OutputDate.setCellStyle(style);

			// Column: result
			HSSFCell headerCell_Result = row_header_1.createCell(COL_11);
			headerCell_Result.setCellValue(getText("reportByDepartment.excel.table.header.RESULT"));
			headerCell_Result.setCellStyle(style);
		}

		
	}
	
	private final static int COL_1 = 0;
	private final static int COL_2 = 1;
	private final static int COL_3 = 2;
	private final static int COL_4 = 3;
	private final static int COL_5 = 4;
	private final static int COL_6 = 5;
	private final static int COL_7 = 6;
	private final static int COL_8 = 7;
	private final static int COL_9 = 8;
	private final static int COL_10 = 9;
	private final static int COL_11 = 10;
	private final static int FONT_SIZE = 11;
	
	
	
	/***
	 * Generate report for one department
	 * @param wb
	 * @param sheet
	 * @param deptReport
	 */
	private void generateDeptReport(HSSFWorkbook wb,HSSFSheet sheet,FileReportBean deptReport,int cursorLocation,String reportType){
		Font header_font = wb.createFont();
		header_font.setFontHeightInPoints((short)FONT_SIZE);
		header_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
		header_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		Font body_font = wb.createFont();
		body_font.setFontHeightInPoints((short)FONT_SIZE);
		body_font.setFontName(WebConstants.FONT_TIMES_NEW_ROMAN);
		
		
		// Fonts are set into a style so create a new one to use.
		HSSFCellStyle BODY_STYLE = wb.createCellStyle();
		BODY_STYLE.setFont(body_font);
		BODY_STYLE.setBorderLeft(CellStyle.BORDER_THIN);
		BODY_STYLE.setBorderBottom(CellStyle.BORDER_THIN);
		BODY_STYLE.setBorderRight(CellStyle.BORDER_THIN);
		BODY_STYLE.setBorderTop(CellStyle.BORDER_THIN);
		BODY_STYLE.setWrapText( true );
		
		HSSFCellStyle HEADER_STYLE = wb.createCellStyle();
		HEADER_STYLE.setFont(header_font);
		HEADER_STYLE.setWrapText( true );
		
		//HEADER
		generateTableHeader(sheet,HEADER_STYLE,cursorLocation,reportType,deptReport.getDepartment());
		
		List<FileBean> lstFile = deptReport.getLstFile();
		
		//BODY
		if(lstFile.size()!=0){
			int rowcount = 0;
			for (Iterator<FileBean> iterator = lstFile.iterator(); iterator.hasNext();) {
				FileBean fileBean = (FileBean) iterator.next();

				HSSFRow row = sheet.createRow(cursorLocation+rowcount+2);
				
				//Column: STT
				HSSFCell cell_stt = row.createCell(COL_1);
				cell_stt.setCellValue(rowcount+1);
				cell_stt.setCellStyle(BODY_STYLE);
				
				// Column: Sender
				HSSFCell cell_fullname = row.createCell(COL_2);
				cell_fullname.setCellValue(fileBean.getSender().toUpperCase());
				cell_fullname.setCellStyle(BODY_STYLE);
				
				// Column: Location
				if(fileBean.getLocation()!=null){
					HSSFCell cell_location = row.createCell(COL_3);
					cell_location.setCellValue(fileBean.getLocation());
					cell_location.setCellStyle(BODY_STYLE);
				}
				
				// Column: Category
				HSSFCell cell_category = row.createCell(COL_4);
				
				//Updated by Thond, on 2012.03.30
				//cell_category.setCellValue(fileBean.getCategory().getShortName());
				cell_category.setCellValue(fileBean.getCategory());
				cell_category.setCellStyle(BODY_STYLE);
				
				//Column: Input date
				HSSFCell cell_receivedOnegate = row.createCell(COL_5);
				cell_receivedOnegate.setCellValue(fileBean.getInputDate());
				cell_receivedOnegate.setCellStyle(BODY_STYLE);

				//Column: Received date at onegate department
				HSSFCell cell_receivedDateProcess = row.createCell(COL_6);
				cell_receivedDateProcess.setCellValue(fileBean.getDateReceiveFileProcess());
				cell_receivedDateProcess.setCellStyle(BODY_STYLE);
				
				//Column: Received date at onegate department
				HSSFCell cell_receiverProcess = row.createCell(COL_7);
				cell_receiverProcess.setCellValue(fileBean.getWhoReceiveFileProcess());
				cell_receiverProcess.setCellStyle(BODY_STYLE);
				
				if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_NOT_APPROVED)){
					//TODO: exclude 2 columns: received date and receiver of onegate
					//Column: return date
					if(fileBean.getOutputDate()!=null){
						HSSFCell cell_outputDate = row.createCell(COL_8);
						cell_outputDate.setCellValue(fileBean.getOutputDate());
						cell_outputDate.setCellStyle(BODY_STYLE);
					}
					
					// Column: result
					HSSFCell cell_result = row.createCell(COL_9);
					cell_result.setCellValue(fileBean.getResult());
					cell_result.setCellStyle(BODY_STYLE);
					
				}else if(reportType.equals(WebConstants.REPORT_TYPE_PROCESS_OVER_TIME)){
					//TODO: include 2 columns: received date and receiver of onegate
					
					if(fileBean.getInputDate()!=null){
						//Column: Received date
						HSSFCell cell_inputdate = row.createCell(COL_8);
						cell_inputdate.setCellValue(fileBean.getDateReceiveFileOnegate());
						cell_inputdate.setCellStyle(BODY_STYLE);
					}
					
					//Column: Receiver
					HSSFCell cell_receiverOnegate = row.createCell(COL_9);
					cell_receiverOnegate.setCellValue(fileBean.getWhoReceiveFileOnegate());
					cell_receiverOnegate.setCellStyle(BODY_STYLE);
					
					//Column: return date
					if(fileBean.getOutputDate()!=null){
						HSSFCell cell_outputDate = row.createCell(COL_10);
						cell_outputDate.setCellValue(fileBean.getOutputDate());
						cell_outputDate.setCellStyle(BODY_STYLE);
					}
					
					// Column: result
					HSSFCell cell_result = row.createCell(COL_11);
					cell_result.setCellValue(fileBean.getResult());
					cell_result.setCellStyle(BODY_STYLE);

				}
				
				rowcount++;
			}
		}
		/* BODY */
	}

	String getExcelContentType() {
		return documentExcelFormat == "xls" ? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" : "application/vnd.ms-excel";
	}
	
	public InputStream getExcelStream() {
		return excelStream;
	}

	public void setReportType(String reportType) {
		this.reportType = reportType;
	}

	public String getReportType() {
		return reportType;
	}

	public void setDeptId(String deptId) {
		this.deptId = deptId;
	}

	public String getDeptId() {
		return deptId;
	}

	public void setLstDepartment(List<Department> lstDepartment) {
		this.lstDepartment = lstDepartment;
	}

	public List<Department> getLstDepartment() {
		return this.departmentManager.getList();
	}

	public void setDepartmentManager(DepartmentManager departmentManager) {
		this.departmentManager = departmentManager;
	}

	public DepartmentManager getDepartmentManager() {
		return departmentManager;
	}
}
